package ict;

import ict.mobile.R;
import android.app.Activity;
import android.os.Bundle;

import android.app.Activity;
import android.os.Bundle;
import android.database.Cursor;
import android.database.sqlite.*;
import android.view.*;
import android.widget.TextView;
import android.widget.Toast;

public class mobileActivity extends Activity {
	SQLiteDatabase db;
	String sql;
	Cursor cursor = null;
	String[] columns = { "mid", "name", "password", "age" };
	String dataStrHeader = String.format("%4s %-12s %-9s %3s\n", "Mid", "Name",
			"Password", "Age");
	String dataStr;
	TextView displayTV;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);

		findViewBy();
		//dropDB();
		createDB();
		finish();
		//selectAllFromStaff();
		//d1();

	}

	// ////find view by ID
	public void findViewBy() {
		displayTV = (TextView) findViewById(R.id.displayTV);

	}

	
	public void onDisplay(View view) {
		//dropDB();
		//d1();	
		//selectAllFromStaff();
		//selectAllFromProduct();
		//selectAllFromStaff();
		selectAllFromproductOrder();	
		// Toast.makeText(this, "OK", 1).show();
	}

	// ////Display All of the database
	public void selectAllFromStaff(){
		try {
			db = SQLiteDatabase.openDatabase("/data/data/ict.mobile/icsDB",
					null, SQLiteDatabase.OPEN_READONLY);

			cursor = db.rawQuery("select * from Staff", null);
			
			String dataStr = String.format("%4s %-10s %7s\n", "StaffID", "LoginName",
					"LoginPswd");
			while (cursor.moveToNext()) {
				int StaffID = cursor.getInt(cursor.getColumnIndex("StaffID"));
				// or cursor.getInt(0);
				String LoginName = cursor.getString(cursor.getColumnIndex("LoginName"));
				// or cursor.getString(3);
				String LoginPswd = cursor.getString(cursor
						.getColumnIndex("LoginPswd"));
				// or cursor.getString(4);
				dataStr += String.format("%4d %-10s %4s\n", StaffID, LoginName, LoginPswd);
			}
			displayTV.setText(dataStr);
			db.close();

		} catch (SQLiteException e) {
			Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
		}

	}
	
	/////display Product
	public void selectAllFromProduct(){
		try {
			db = SQLiteDatabase.openDatabase("/data/data/ict.mobile/icsDB",
					null, SQLiteDatabase.OPEN_READONLY);

			cursor = db.rawQuery("select * from Product", null);
			//Toast.makeText(this, "OK", Toast.LENGTH_LONG).show();
			
			//ProductCode, ProductName, Quantity
			String dataStr = String.format("%4s %-10s %7s\n", "ProductCode", "ProductName",
					"Quantity");
			while (cursor.moveToNext()) {
				// or cursor.getInt(0);
				String ProductCode = cursor.getString(0);
				String ProductName = cursor.getString(1);
				int Quantity = cursor.getInt(2);
				dataStr += ProductCode + ProductName + Quantity;
			}
			
			displayTV.setText(dataStr);
			
			db.close();

		} catch (SQLiteException e) {
			Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
		}

	}
	

	/////display shop 
	public void selectAllFromShop(){
		try {
			db = SQLiteDatabase.openDatabase("/data/data/ict.mobile/icsDB",
					null, SQLiteDatabase.OPEN_READONLY);

			cursor = db.rawQuery("select * from Shop", null);
			
			//ShopCode, ShopName, ShopManager
			String dataStr = String.format("%4s %-10s %7s\n", "ShopCode", "ShopName",
					"ShopManager");
			while (cursor.moveToNext()) {
				// or cursor.getInt(0);
				String ShopCode = cursor.getString(0);
				String ShopName = cursor.getString(1);
				int ShopManager = cursor.getInt(2);
				// or cursor.getString(3);
				/*
				String ProductName = cursor.getString(cursor.getColumnIndex("ProductName"));
				
				int Quantity = cursor.getInt(cursor.getColumnIndex("Quantity"));
				
				// or cursor.getString(4);
				 * 
				 */
				//dataStr += String.format("%11d %-6s %4s\n", ProductCode, ProductName, Quantity);
				dataStr += ShopCode + ShopName + ShopManager;
			}
			displayTV.setText(dataStr);
			
			db.close();

		} catch (SQLiteException e) {
			Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
		}

	}
	/////display productOrder
	public void selectAllFromproductOrder(){
		try {
			db = SQLiteDatabase.openDatabase("/data/data/ict.mobile/icsDB",
					null, SQLiteDatabase.OPEN_READONLY);

			cursor = db.rawQuery("select * from productOrder", null);
			

			//"CREATE TABLE productOrder (OrderID text PRIMARY KEY, ShopCode text, StaffID int, LoginName text, date text, prod01 int, prod02 int , prod03 int , prod04 int,  prod05 int);";
			String dataStr = "";
			while (cursor.moveToNext()) {
				// or cursor.getInt(0);
				String OrderID = cursor.getString(0);
				String ShopCode = cursor.getString(1);
				int StaffID = cursor.getInt(2);
				String LoginName = cursor.getString(3);
				String date = cursor.getString(4);
				String prod01 = cursor.getString(5);
				String prod02 = cursor.getString(6);
				String prod03 = cursor.getString(7);
				String prod04 = cursor.getString(8);
				String prod05 = cursor.getString(9);
				// or cursor.getString(3);
				/*
				String ProductName = cursor.getString(cursor.getColumnIndex("ProductName"));
				
				int Quantity = cursor.getInt(cursor.getColumnIndex("Quantity"));
				
				// or cursor.getString(4);
				 * 
				 */
				//dataStr += String.format("%11d %-6s %4s\n", ProductCode, ProductName, Quantity);
				dataStr += OrderID + ShopCode + StaffID + LoginName+ date + prod01 + prod02 + prod03 + prod04 + prod05;
			}
			displayTV.setText(dataStr);
			
			db.close();

		} catch (SQLiteException e) {
			Toast.makeText(this, e.getMessage(), Toast.LENGTH_LONG).show();
		}

	}
	// /Create the Database
	public void createDB() {

		// filePath is a complete destination of the form
		// "/data/data/<namespace>/<databaseName>"
		try {
			db = SQLiteDatabase.openDatabase("/data/data/ict.mobile/icsDB",
					null, SQLiteDatabase.CREATE_IF_NECESSARY);
			
			
			////create Staff
			sql = "DROP TABLE IF EXISTS Staff;";
			db.execSQL(sql);
			sql = "CREATE TABLE Staff (StaffID int PRIMARY KEY, LoginName text, LoginPswd text);";
			db.execSQL(sql);
			db.execSQL("INSERT INTO Staff(StaffID, LoginName, LoginPswd) values"
					+ "(1101, 'staff01', 'pswd01'); ");
			db.execSQL("INSERT INTO Staff(StaffID, LoginName, LoginPswd) values"
					+ "(1102, 'staff02', 'pswd02'); ");
			db.execSQL("INSERT INTO Staff(StaffID, LoginName, LoginPswd) values"
					+ "(1103, 'staff03', 'pswd03'); ");
			db.execSQL("INSERT INTO Staff(StaffID, LoginName, LoginPswd) values"
					+ "(1104, 'staff04', 'pswd04'); ");
			
			//create product
			sql = "DROP TABLE IF EXISTS Product;";
			db.execSQL(sql);
			sql = "CREATE TABLE Product (ProductCode text PRIMARY KEY, ProductName text, Quantity int);";
			db.execSQL(sql);
			db.execSQL("INSERT INTO Product(ProductCode, ProductName, Quantity) values"
					+ "('prod01', 'Orange Juice', 200); ");
			db.execSQL("INSERT INTO Product(ProductCode, ProductName, Quantity) values"
					+ "('prod02', 'Milk', 250); ");
			db.execSQL("INSERT INTO Product(ProductCode, ProductName, Quantity) values"
					+ "('prod03', 'Apple Juice', 150); ");
			db.execSQL("INSERT INTO Product(ProductCode, ProductName, Quantity) values"
					+ "('prod04', 'Ice Tea', 100); ");
			db.execSQL("INSERT INTO Product(ProductCode, ProductName, Quantity) values"
					+ "('prod05', 'Mineral Water', 300); ");
			
			
			////create shop   //ShopCode ShopName Shop Manager (StaffID)
			sql = "DROP TABLE IF EXISTS Shop;";
			db.execSQL(sql);
			sql = "CREATE TABLE Shop (ShopCode text PRIMARY KEY, ShopName text, ShopManager int);";
			db.execSQL(sql);
			db.execSQL("INSERT INTO Shop(ShopCode, ShopName, ShopManager) values"
					+ "('shop01', 'Causeway Bay', 1202); ");
			db.execSQL("INSERT INTO Shop(ShopCode, ShopName, ShopManager) values"
					+ "('shop02', 'Mongkok', 1303); ");
			db.execSQL("INSERT INTO Shop(ShopCode, ShopName, ShopManager) values"
					+ "('shop03', 'Tsim Sha Tsui', 1101); ");
			
			////create Order   
			sql = "DROP TABLE IF EXISTS productOrder;";
			db.execSQL(sql);
			
			sql = "CREATE TABLE productOrder (OrderID text PRIMARY KEY, ShopCode text, StaffID int, LoginName text, date text, prod01 int, prod02 int , prod03 int , prod04 int,  prod05 int);";
			//sql = "CREATE TABLE productOrder (OrderID text PRIMARY KEY, ShopName text);";
			db.execSQL(sql);
			
			/*
			db.execSQL("INSERT INTO productOrder(OrderID, ShopCode, StaffID, date, LoginName, prod01 ,prod02, prod03 ,prod04 ,prod05) values"
					+ "('order66', 'Kwoloon', 1101, 'staff01', '2012-1-1', 1, 2, 3, 4, 5); ");
					*/
					
			db.close();
		} catch (SQLiteException e) {
			Toast.makeText(this, e.getMessage(), 1).show();
		}
	}
	
	public void d1(){
		try {
			db = SQLiteDatabase.openDatabase("/data/data/ict.mobile/icsDB",
					null, SQLiteDatabase.CREATE_IF_NECESSARY);
			//db.execSQL("INSERT INTO Staff(StaffID, LoginName, LoginPswd) values"
			//		+ "(1102, 'staff02', 'pswd02'); ");
			db.execSQL("INSERT INTO Staff(StaffID, LoginName, LoginPswd) values"
					+ "(1103, 'staff03', 'pswd03'); ");
			//displayTV.setText(dataStr);

			db.close();
		} catch (SQLiteException e) {
			Toast.makeText(this, e.getMessage(), 1).show();
		}
	}
	
	@Override
	public void finish() {
		Toast.makeText(this, "You now complete DataSynchronization!",
				Toast.LENGTH_LONG).show();
		super.finish();
	}	
}